package com.coderdream.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.coderdream.bean.Student;
import com.coderdream.util.DBUtil;

public class StudentDao {

	public static String TAG = "StudentDao";

	public Logger logger = Logger.getLogger(StudentDao.class);

	private PreparedStatement pre;
	private ResultSet rs;

	/**
	 * @author help
	 * 
	 *         插入新的一条记录
	 * @return
	 */
	public int getInsert(String sql, Student student) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setString(1, student.getName());
			pre.setString(2, student.getPassword());
			pre.setString(3, student.getSex());
			pre.setInt(4, student.getAge());
			count = pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         删除一条记录
	 * @return
	 */
	public int getDelete(String sql, int id) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			count = pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         根据ID查询某一条记录
	 * @return
	 */
	public Student getSearchById(String sql, int id) {
		Student student = new Student();
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs = pre.executeQuery();

			while (rs.next()) {
				student.setId(rs.getInt("id"));
				student.setName(rs.getString("name"));
				student.setPassword(rs.getString("password"));
				student.setSex(rs.getString("sex"));
				student.setAge(rs.getInt("age"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return student;
	}

	/**
	 * @author help
	 * 
	 *         更新某一条记录
	 * @return
	 */
	public int getUpdate(String sql, Student student) {
		int count = 0;
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(5, student.getId());
			pre.setString(1, student.getName());
			pre.setString(2, student.getPassword());
			pre.setString(3, student.getSex());
			pre.setInt(4, student.getAge());
			count = pre.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return count;
	}

	/**
	 * @author help
	 * 
	 *         显示所有记录
	 * @return
	 */
	public List<Student> getSearch(String sql) {
		List<Student> list = new ArrayList<Student>();
		// 获取prepareStatement对象
		Connection con = null;
		try {
			con = DBUtil.getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();

			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getInt("id"));
				student.setName(rs.getString("name"));
				student.setPassword(rs.getString("password"));
				student.setSex(rs.getString("sex"));
				student.setAge(rs.getInt("age"));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pre);
			DBUtil.close(con);
		}
		return list;
	}

}